

Create PROCEDURE [dbo].[CXYL_PickMat_proce]
	@no varchar(200),
	@ser int
as
begin
declare 
@entyId int,
@ppEntyId int,
@ppNo varchar(200),
@org int,
@stockId int,
@matId int,
@matNumber varchar(200),
@cwId int,
@ylQty decimal(18,6),
@jskc decimal(18,6),
@jskc1 decimal(18,6),
@jskc2 decimal(18,6)
select top 1
@entyId= FENTRYID
,@ppEntyId= FPPBOMENTRYID
,@ppNo= FPPBOMBILLNO
,@org= FSTOCKORGID
,@stockId=FSTOCKID
,@matId=mat.FMATERIALID
,@matNumber=mat.FNUMBER
,@cwId= FSTOCKLOCID
 from T_PRD_PICKMTRLDATA enty
left join T_PRD_PICKMTRL main on enty.FID=main.FID
left join T_BD_MATERIAL mat on enty.FMATERIALID=mat.FMATERIALID
where main.FBILLNO=@no
and FSEQ=@ser
if(@entyId<>0)
begin
select @ylQty= SUM(enty.FBASEQTY) from T_PLN_RESERVELINK main
left join T_PLN_RESERVELINKENTRY enty on main.FID=enty.FID 
left join T_PRD_PPBOMENTRY ppEnty on main.FDEMANDENTRYID =ppEnty.FENTRYID
left join T_PRD_PPBOM ppMain on ppEnty.FID=ppMain.FID
where main.FDEMANDBILLNO=@ppNo
and main.FDEMANDENTRYID=@ppEntyId
and main.FDEMANDFORMID='PRD_PPBOM'
and enty.FSUPPLYFORMID='STK_Inventory'
and enty.FSUPPLYORGID=@org
and enty.FSTOCKID=@stockId
and enty.FMATERIALID=@matId
and enty.FSTOCKLOCID=@cwId

select @jskc1= SUM(enty.FBASEQTY) 
from T_STK_INVENTORY enty
where enty.FSTOCKORGID=@org 
and enty.FSTOCKID=@stockId
and enty.FMATERIALID=@matId
and enty.FSTOCKLOCID=@cwId

select @jskc2= SUM(enty.FBASEQTY) from T_PLN_RESERVELINKENTRY enty
where enty.FSUPPLYFORMID='STK_Inventory'
and enty.FSUPPLYORGID=@org
and enty.FSTOCKID=@stockId
and enty.FMATERIALID=@matId
and enty.FSTOCKLOCID=@cwId

select 
entyId= @entyId
,ppEntyId= @ppEntyId
,ppNo= @ppNo
,org= @org
,stockId=@stockId
,matId=@matId
,matNumber=@matNumber
,cwId= @cwId
,ylQty=ISNULL(@ylQty,0)
,jskc=ISNULL(@jskc1,0)-ISNULL(@jskc2,0)
,jskc1=@jskc1
,jskc2=@jskc2
end

end
